﻿using MySql.Data.MySqlClient;
using System;

namespace OrderManagement
{
    public class OrderService
    {
        static string connectionStr = "data source = localhost;database = db1;user id = root;password = 06li..123;pooling = false;charset = utf8";
        MySqlConnection conn = new MySqlConnection(connectionStr);

        public void add(Order order)
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;

            string name = order.CommodityName!;
            string customer = order.Customer!;
            double price = order.Price;

            string sql = "insert into tb_order values(NULL, '" + name + "', '" + customer + "','" + price + "')";


            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            conn.Close();
        }

        public void delete(int id)
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;

            string sql = "DELETE FROM tb_order WHERE id='"+id+"'";


            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            conn.Close();
        }


        public Order selectById(int id)
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;

            string sql = "DELETE FROM tb_order WHERE id='" + id + "'";


            cmd.CommandText = sql;
            Order order = (Order)cmd.ExecuteScalar();

            conn.Close();

            return order;
        }

        public void update(Order order1)
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;

            int id = order1.Id;
            string commodityName = order1.CommodityName!;
            string customer = order1.Customer!;
            double price = order1.Price!;


            string sql = "UPDATE FROM tb_order SET commodityName = '"+commodityName+ "', customer = '" + customer + "',price = '" + price + "' WHERE id='" + id + "'";


            cmd.CommandText = sql;
            Order order = (Order)cmd.ExecuteScalar();

            conn.Close();
        }
    }
}